package cs304.p3.library.util;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import com.google.common.collect.Lists;

public class SqlUtils {	
	public static final String ANY_VALUE = "#ANY#";
	public static final String NULL = "NULL";
	
	public static String selectSql(String table) {
		return "SELECT * FROM "+table;
	}
	
	public static String selectSql(String table, Map<String,String> keyVals) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT * FROM "+table);
		sql.append(" WHERE ");
		sql.append(SqlUtils.whereStrVal(keyVals));
		return sql.toString();
	}
	
	public static String insertSql(String table, List<String> values) {
		StringBuilder sql = new StringBuilder("INSERT INTO "+table+" VALUES");
		sql.append(SqlUtils.commaQuoteStrVal(values));
		return sql.toString();
	}
	
	public static String insertSql(String table, Map<String,String> keyVals) {
		List<String> keys = Lists.newArrayList(keyVals.keySet());
		StringBuilder sql = new StringBuilder("INSERT INTO "+table+commaStrVal(keys)+" VALUES");
		List<String> values = Lists.newArrayList();
		
		for(String key : keys) {
			values.add(keyVals.get(key));
		}
		
		sql.append(SqlUtils.commaQuoteStrVal(values));
		return sql.toString();
	}

	public static String commaQuoteStrVal(List<String> values) {
		filterAnyValue(values);
		StringBuilder sb = new StringBuilder("(");
		for(int i=0; i<values.size(); i++) {
			String value = quoteStr(values.get(i));

			if(i != 0)
				sb.append(", ");	
			sb.append(value);
		}
		sb.append(")");
		return sb.toString();
	}
	
	public static String commaStrVal(List<String> values) {
		filterAnyValue(values);
		StringBuilder sb = new StringBuilder("(");
		for(int i=0; i<values.size(); i++) {
			String value = (values.get(i));

			if(i != 0)
				sb.append(", ");	
			sb.append(value);
		}
		sb.append(")");
		return sb.toString();
	}

	public static String quoteStr(String value) {
		return "'"+value+"'";
	}

	public static String whereStrVal(Map<String,String> keyVals) {
		filterAnyValue(keyVals);
		StringBuilder sb = new StringBuilder();
		int i=0;
		for(String key : keyVals.keySet()) {
			String value = keyVals.get(key);
			if(i!=0) {
				sb.append(" AND ");
			}
			sb.append(key+"="+quoteStr(value));
			i++;
		}
		return sb.toString();
	}
	
	public static String setStrVal(Map<String,String> keyVals) {
		filterAnyValue(keyVals);
		StringBuilder sb = new StringBuilder();
		int i=0;
		for(String key : keyVals.keySet()) {
			String value = keyVals.get(key);
			if(i!=0) {
				sb.append(", ");
			}
			sb.append(key+"="+quoteStr(value));
			i++;
		}
		return sb.toString();
	}

	public static void filterAnyValue(List<String> values) {
		for(Iterator<String> it = values.iterator(); it.hasNext();) {
			String value = it.next();
			if(value.equals(ANY_VALUE))
				it.remove();
		}
	}

	public static void filterAnyValue(Map<String,String> keyVals) {
		List<String> removeKeys = Lists.newArrayList();
		for(String key : keyVals.keySet()) {
			String value = keyVals.get(key);
			if(value.equals(ANY_VALUE))
				removeKeys.add(key);
		}
			
		for(String key : removeKeys) {
			keyVals.remove(key);
		}
	}
	
	public static String getDateString(Date date) {
		SimpleDateFormat sdf = new SimpleDateFormat("yy-MMM-dd");
		return sdf.format(date);
	}
	
	public static Date getDate(String date) {
		SimpleDateFormat sdf = new SimpleDateFormat("yy-MMM-dd");
		Date d = null;
		try {
			d = sdf.parse(date);
		} catch (ParseException e) {
			System.err.println(e.getMessage());
			throw new RuntimeException();
		}
		
		return d;
	}
	
	public static String updateSql(String tablename, Map<String,String> updateVals, Map<String,String> whereVals) {
		StringBuilder sql = new StringBuilder("UPDATE "+tablename+" SET ");
		sql.append(setStrVal(updateVals));
		sql.append(" WHERE ");
		sql.append(whereStrVal(whereVals));
		
		return sql.toString();
	}
	
	public static String deleteSql(String table, Map<String,String> keyVals) {
		StringBuilder sql = new StringBuilder();
		sql.append("DELETE FROM "+table);
		sql.append(" WHERE ");
		sql.append(SqlUtils.whereStrVal(keyVals));
		return sql.toString();
	}
}
